import openpyxl
wb =openpyxl.load_workbook('example.xlsx')

#print(wb.sheetnames)
for sheet in wb.sheetnames:
    print(sheet)
if not 'testNew' in wb.sheetnames:
    newSheet = wb.create_sheet('testNew')
    print(newSheet.title)
wb.remove_sheet()
wb.save("example_copy.xlsx")
#获取worksheet
obj = wb.active
print(type(obj))
#获取worksheet
ws = wb.worksheets[2]
print(type(ws))
print(ws)
#获取worksheet
ws2= wb.get_sheet_by_name('Sheet2')
print(ws2)

#根据坐标获取单元格
print(ws['B1'].value)
#print(wb.sheetnames)
#wb.save('example.xlsx')

# cell=ws['B1']
# print('row :{} colu:{} is {}'.format(cell.row,cell.column,cell.value))
# print('cell {} is {}'.format(cell.coordinate,cell.value))
# 根据 行列索引 获取单元格
print(ws.cell(column=2,row=2).value)


# 获取一个区间
# 获取一整行
rowC=ws[1]
# print(type(rowC))
print(rowC)
for rowitem in rowC:
    print(rowitem)
    print(rowitem.value)

# 获取一整行列
coluC = ws['B']
# print(type(coluC))
print(coluC)
for coluitem in coluC:
    print(coluitem)
    print(coluitem.value)

#获取一个矩形区间
col_range=ws['B:C']
row_range=ws[2:6]

for col in col_range:
    for cell in col:
        print(cell.value)

for r in row_range:
    for cell in r:
        print(cell.value)
#
print('--------------------')
cell_range=ws['A1:B3']
for rowOfCellObjs in cell_range:
    for rowObj in rowOfCellObjs:
        print(rowObj.coordinate,rowObj.value)

#
print('--------------------')
for row in ws.iter_rows(min_row=1,max_row=2,max_col=2):
    for cell in row:
        print(cell.value)

print('--------------------')
print(tuple(ws.rows))

print('--------------------')
print('max_column:{}  max_row:{}'.format(ws.max_column,ws.max_row))

#字母转数字
from openpyxl.utils import  get_column_letter,column_index_from_string
print(get_column_letter(35),get_column_letter(356))
print(column_index_from_string('AI'),column_index_from_string('MR'))
